Today, the amount of data stored and processed by database systems, in particular by relational database systems, grows at an accelerating rate. Due to the ever growing data volume, access times to data stored in the database are continuously growing. This puts an enormous pressure on database administrators to optimize the functionality and performance of database systems.
Nowadays, many database management systems are equipped with related optimization and monitoring tools. The purpose of database management systems is to organize data and allowing quick and convenient access to retrieve stored data. There are various types of database management systems, such as relational database management systems, hierarchical database management systems, and network database management systems. Typical data access statements may comprise read, write, update and delete statements.
However, monitoring and tracking the internal functionality of a database requires additional computing power which may negatively influence the overall performance of the database management system. A well-known technique for measuring the performance of a database management system relates to snapshot data. Snapshot data deliver continuous information about the internal functionality of a database. For example, times to store data, required times to find and read data and/or times to delete data are continuously measured. Typically, a snapshot data record represents a large amount of data about the internal functionality and actual status of a database management system. Other data are related to events in the database, e.g., a notification about started and finished SQL statements or other events of similar nature. Alerting may also be related to specific events
On the other side, stored procedures became very popular in order to offload logical operations from application programs. Meanwhile, such application logic may now be executed as part of the database—much closer to the data—in stored procedures. Often, loops are being executed in stored procedures. And typically, only execution times for complete stored procedures may be measured as part of global snapshot and event data. In order to optimize the functionality and performance of a database it may be required to have much more fine-granular information about execution of SQL statements within a stored procedure. On the other side, such close monitoring is equivalent to additional overhead for a constant measurement of performance data of the database.
A query language is typically used to access the data and the database management system. Database application programs can be written using the query language to access the data stored in the database. For example, the structured query language (SQL) is one well-known query language. The database application program may be written using SQL in combination with other programming languages such as COBOL, PL/1, JAVA, and C, to access the data stored in the database.
A module, referred to as a “stored procedure” (SP), can be used to access the data in one or more database management systems. The stored procedure may be a program or a script file, and is physically stored at a database management system, typically the database management system on which the stored procedure may be accessed. The database application program may invoke or call one or more stored procedures. The stored procedure typically may comprise one or more data access statements (SQL statements), or calls to other stored procedures for issue data requests, for data from one or more databases (nested stored procedures). For example, the stored procedure may comprise one or more SQL statements to retrieve data from the database management system. Stored procedures may be shared with multiple database application programs.
Today's monitoring support for stored procedures may deal with two main problem areas. Firstly, for most database administrators the stored procedure may be a “black box” from a monitoring perspective. They may not know what is happening “inside” a stored procedure. Secondly, in order to get a certain level of detail today, a database administrator may need to take up “very expensive” tracing with significant CPU overhead. As a further step a manual match of the detailed information collected, for example, about statements executed in the stored procedure, may need to be carried out. This may be a very expensive, time-consuming and error-prone process. Furthermore, event and snapshot data collection may not take the time into account that is spent in the application logic of the stored procedure.
Thus, there may be a need for an improved performance management method for stored procedures in a database management system without an additional performance burden caused by collecting additional performance data within a stored procedure.